﻿Imports System.Data.SqlClient
Imports System.Linq
Public Class frmMain
    Dim availability As Dictionary(Of Char, String)
    Dim CN As New SqlConnection
    Private Sub mnuExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuExit.Click
        End
    End Sub

    Private Sub mnuStudents_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuStudents.Click
        frmStudent.Show(Me)
    End Sub

    Private Sub mnuQualifications_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuQualifications.Click
        frmQualifications.Show(Me)
    End Sub

    Private Sub mnuTeacherInformation_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuTeacherInformation.Click
        frmTeachers.Show(Me)
    End Sub

    Private Sub mnuPrograms_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuPrograms.Click
        frmPrograms.Show(Me)
    End Sub

    Private Sub mnuCourses_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuCourses.Click
        frmCourses.Show(Me)
    End Sub

    Private Sub mnuPrerequisites_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuPrerequisites.Click
        frmPrerequisites.Show(Me)
    End Sub

    Private Sub mnuClassrooms_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuClassrooms.Click
        frmClassrooms.Show(Me)
    End Sub

    Private Sub mnuEquipment_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuEquipment.Click
        frmEquipment.Show(Me)
    End Sub

    Private Sub btnGenerate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenerate.Click
        'WB - creates the connection to database
        Dim LI As New ListViewItem
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "CreateClassList"
        cmd.CommandType = CommandType.StoredProcedure
        Dim Reader As SqlDataReader = Nothing
        cmd.Parameters.AddWithValue("@minCount", nudMin.Value)

        'WB - tries to open the connection, reports an error it it fails
        Try
            CN.Open()
        Catch ex As Exception
            MessageBox.Show("Could not connect to the Database - " & ex.Message, "Error")
        End Try

        'WB - clears all the items out of lsvCourse
        lsvCourse.Items.Clear()
        cboTeacher.SelectedIndex = -1
        cboTeacher.Items.Clear()
        lstStudents.Items.Clear()
        'WB - calls the procedure to generate the possibilites and places the courses in lsvCourses
        Try
            Reader = cmd.ExecuteReader
            While Reader.Read
                LI = New ListViewItem
                LI.Text = Convert.ToString(Reader.Item("CourseID"))
                LI.SubItems.Add(availability(Convert.ToChar(Reader.Item("ClassTime"))))
                LI.SubItems.Add(Convert.ToString(Reader.Item("StudentCount")))
                lsvCourse.Items.Add(LI)
            End While
            Reader.Close()
        Catch ex As Exception
            MessageBox.Show("Cannot Generate Schedule - " & ex.Message, "Error")
        Finally
            Try
                Reader.Close()
            Catch ex As Exception
            End Try
            If CN.State = ConnectionState.Open Then CN.Close()
            End Try
    End Sub

    Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        CN.ConnectionString = My.Settings.IT308ConnectionString
        availability = New Dictionary(Of Char, String)
        availability.Add(CChar("a"), "Monday Morning")
        availability.Add(CChar("b"), "Monday Afternoon")
        availability.Add(CChar("c"), "Monday Evening")
        availability.Add(CChar("d"), "Tuesday Morning")
        availability.Add(CChar("e"), "Tuesday Afternoon")
        availability.Add(CChar("f"), "Tuesday Evening")
        availability.Add(CChar("g"), "Wednesday Morning")
        availability.Add(CChar("h"), "Wednesday Afternoon")
        availability.Add(CChar("i"), "Wednesday Evening")
        availability.Add(CChar("j"), "Thursday Morning")
        availability.Add(CChar("k"), "Thursday Afternoon")
        availability.Add(CChar("l"), "Thursday Evening")
        availability.Add(CChar("m"), "Friday Morning")
        availability.Add(CChar("n"), "Friday Afternoon")
        availability.Add(CChar("o"), "Friday Evening")

        load_lsvCreatedClasses()
    End Sub

    Private Sub lsvCourse_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lsvCourse.Click
        load_cboTeacher()
        load_lstStudents()
    End Sub
    Private Sub load_cboTeacher()
        'WB - creates the connection to the database. and loads the paramaters for the query
        Dim cmd As New SqlCommand
        Dim foo = From entry In availability Where entry.Value = lsvCourse.SelectedItems(0).SubItems.Item(1).Text Select entry.Key
        cmd.Connection = CN
        cmd.CommandText = "SELECT DISTINCT TEACHER.FName, TEACHER.LName From TEACHER inner join AvailTeachers on TEACHER.ID = AvailTeachers.TeacherID WHERE AvailTeachers.CourseID = @CourseID AND ClassTime = @ClassTime"
        Dim Reader As SqlDataReader = Nothing
        cmd.Parameters.AddWithValue("@CourseID", lsvCourse.SelectedItems(0).SubItems.Item(0).Text)
        cmd.Parameters.AddWithValue("@ClassTime", foo.First.ToString)



        'WB - tries to open the connection and reports the error if it fails.
        Try
            CN.Open()
        Catch ex As Exception
            MessageBox.Show("Cannot connect to Database - " & ex.Message, "Error")
        End Try

        'WB - clears the combo box
        cboTeacher.Items.Clear()
        cboTeacher.SelectedIndex = -1

        'WB - executes the query and loads the teachers names into
        Try
            Reader = cmd.ExecuteReader
            While Reader.Read
                cboTeacher.Items.Add(Convert.ToString(Reader.Item("FName")) & " " & Convert.ToString(Reader.Item("LName")))
            End While
            
        Catch ex As Exception
            MessageBox.Show("Cannot load Teachers - " & ex.Message, "Error")
        Finally
            If CN.State = ConnectionState.Open Then CN.Close()
            Reader.Close()
        End Try

        Try
            'WB - selects the teacher with the highest rating automatically.
            cboTeacher.SelectedIndex = 0
        Catch ex As Exception
            cboTeacher.Items.Clear()
            cboTeacher.Text = ""
            cboTeacher.Refresh()
            MessageBox.Show("No Teacher is available to teach this class is this time slot", "Error")
        End Try
    End Sub
    Private Sub load_lstStudents()
        'WB - creates the connection to the database. and loads the paramaters for the query
        Dim cmd As New SqlCommand
        Dim foo = From entry In availability Where entry.Value = lsvCourse.SelectedItems(0).SubItems.Item(1).Text Select entry.Key
        cmd.Connection = CN
        cmd.CommandText = "SELECT DISTINCT STUDENTS.FName, STUDENTS.LName From STUDENTS inner join AvailClasses on STUDENTS.ID = AvailClasses.StudentID WHERE AvailClasses.CourseID = @CourseID AND ClassTime = @ClassTime"
        Dim Reader As SqlDataReader = Nothing
        cmd.Parameters.AddWithValue("@CourseID", lsvCourse.SelectedItems(0).SubItems.Item(0).Text)
        cmd.Parameters.AddWithValue("@ClassTime", foo.First.ToString)

        'WB - tries to open the connection and reports the error if it fails.
        Try
            CN.Open()
        Catch ex As Exception
            MessageBox.Show("Cannot connect to Database - " & ex.Message, "Error")
        End Try

        'WB - clears the combo box
        lstStudents.Items.Clear()

        'WB - executes the query and loads the Students names into lstStudents
        Try
            Reader = cmd.ExecuteReader
            While Reader.Read
                lstStudents.Items.Add(Convert.ToString(Reader.Item("FName")) & " " & Convert.ToString(Reader.Item("LName")))
            End While
        Catch ex As Exception
            MessageBox.Show("Cannot load Students - " & ex.Message, "Error")
        Finally
            If CN.State = ConnectionState.Open Then CN.Close()
            Reader.Close()
        End Try
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        'WB - creates the connection to the database. and loads the paramaters for the query
        Dim cmd As New SqlCommand
        Dim foo = From entry In availability Where entry.Value = lsvCourse.SelectedItems(0).SubItems.Item(1).Text Select entry.Key
        cmd.Connection = CN
        cmd.CommandText = "CreateClass"
        cmd.CommandType = CommandType.StoredProcedure

        Try
            cmd.Parameters.AddWithValue("@CourseID", lsvCourse.SelectedItems(0).SubItems.Item(0).Text)
            cmd.Parameters.AddWithValue("@ClassTime", foo.First.ToString)
            cmd.Parameters.AddWithValue("@FName", cboTeacher.SelectedItem.ToString.Substring(0, cboTeacher.SelectedItem.ToString.IndexOf(" ")).Trim)
            cmd.Parameters.AddWithValue("@LName", cboTeacher.SelectedItem.ToString.Substring(cboTeacher.SelectedItem.ToString.IndexOf(" ") + 1).Trim)
        Catch ex As Exception
            MessageBox.Show("You must first select a course and a teacher to save a class", "Error")
        End Try
        'WB - tries to open the connection and reports the error if it fails.
        Try
            CN.Open()
        Catch ex As Exception
            MessageBox.Show("Cannot connect to Database - " & ex.Message, "Error")
        End Try

        'WB - executes the query and loads the Students names into lstStudents
        Try
            If cmd.ExecuteNonQuery = 0 Then
                Throw New Exception("Class was not created")
            End If
        Catch ex As Exception
            MessageBox.Show("Cannot Save Class - " & ex.Message, "Error")
        Finally
            If CN.State = ConnectionState.Open Then CN.Close()
        End Try
        btnGenerate_Click(btnSave, e)
        load_lsvCreatedClasses()
    End Sub
    Private Sub load_lsvCreatedClasses()
        'WB - creates the connection to database
        Dim LI As New ListViewItem
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "SELECT CLASSES.CoursesID, CLASSES.ClassTime, TEACHER.FName, TEACHER.LName, count(STUDENTSCLASSES.StudentsID) as StudentCount FROM CLASSES	inner join TEACHER on CLASSES.TeacherID = TEACHER.ID inner join STUDENTSCLASSES on CLASSES.ID = STUDENTSCLASSES.ClassesID GROUP BY CLASSES.COURSESID, CLASSES.ClassTime, TEACHER.LName, TEACHER.FName"
        Dim Reader As SqlDataReader = Nothing


        'WB - tries to open the connection, reports an error it it fails
        Try
            CN.Open()
        Catch ex As Exception
            MessageBox.Show("Could not connect to the Database - " & ex.Message, "Error")
        End Try

        'WB - clears all the items out of lsvCourse
        lsvCreatedClasses.Items.Clear()
        'lstStudents.Items.Clear()

        'WB - calls the procedure to generate the possibilites and places the courses in lsvCourses
        Try
            Reader = cmd.ExecuteReader
            While Reader.Read
                LI = New ListViewItem
                LI.Text = Convert.ToString(Reader.Item("CoursesID"))
                LI.SubItems.Add(availability(Convert.ToChar(Reader.Item("ClassTime"))))
                LI.SubItems.Add(Convert.ToString(Reader.Item("StudentCount")))
                LI.SubItems.Add(Convert.ToString(Reader.Item("FName")) & " " & Convert.ToString(Reader.Item("LName")))
                lsvCreatedClasses.Items.Add(LI)
            End While
            Reader.Close()
        Catch ex As Exception
            MessageBox.Show("Cannot Load Currently Scheduled Classes - " & ex.Message, "Error")
        Finally
            Try
                Reader.Close()
            Catch ex As Exception
            End Try
            If CN.State = ConnectionState.Open Then CN.Close()
        End Try
    End Sub

    Private Sub load_lstClassesStudents()
        'WB - creates the connection to the database. and loads the paramaters for the query
        Dim cmd As New SqlCommand
        Dim foo = From entry In availability Where entry.Value = lsvCreatedClasses.SelectedItems(0).SubItems.Item(1).Text Select entry.Key
        cmd.Connection = CN
        cmd.CommandText = "SELECT DISTINCT STUDENTS.FName, STUDENTS.LName FROM STUDENTSCLASSES inner join STUDENTS on STUDENTS.ID = STUDENTSCLASSES.StudentsID	inner join CLASSES on STUDENTSCLASSES.ClassesID = CLASSES.ID WHERE CLASSES.CoursesID = @CourseID AND ClassTime = @ClassTime"
        Dim Reader As SqlDataReader = Nothing
        cmd.Parameters.AddWithValue("@CourseID", lsvCreatedClasses.SelectedItems(0).SubItems.Item(0).Text)
        cmd.Parameters.AddWithValue("@ClassTime", foo.First.ToString)

        'WB - tries to open the connection and reports the error if it fails.
        Try
            CN.Open()
        Catch ex As Exception
            MessageBox.Show("Cannot connect to Database - " & ex.Message, "Error")
        End Try

        'WB - clears the combo box
        lstClassesStudents.Items.Clear()

        'WB - executes the query and loads the Students names into lstStudents
        Try
            Reader = cmd.ExecuteReader
            While Reader.Read
                lstClassesStudents.Items.Add(Convert.ToString(Reader.Item("FName")) & " " & Convert.ToString(Reader.Item("LName")))
            End While
        Catch ex As Exception
            MessageBox.Show("Cannot load Students - " & ex.Message, "Error")
        Finally
            If CN.State = ConnectionState.Open Then CN.Close()
            Reader.Close()
        End Try
    End Sub

    Private Sub lsvCreatedClasses_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lsvCreatedClasses.Click
        load_lstClassesStudents()
    End Sub

    Private Sub lsvCreatedClasses_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles lsvCreatedClasses.DoubleClick
        Dim foo = From entry In availability Where entry.Value = lsvCreatedClasses.SelectedItems(0).SubItems.Item(1).Text Select entry.Key

        Dim frmEditClasses As New frmEditClasses(lsvCreatedClasses.SelectedItems(0).SubItems.Item(0).Text, CChar(foo.First.ToString))
        frmEditClasses.ShowDialog()
        load_lsvCreatedClasses()
        btnGenerate_Click(lsvCreatedClasses, e)
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If MessageBox.Show("Are you sure you want to delete " & lsvCreatedClasses.SelectedItems(0).SubItems.Item(0).Text & "?", "Confirm", MessageBoxButtons.OKCancel) = Windows.Forms.DialogResult.OK Then


            'WB - creates the connection to the database. and loads the paramaters for the query
            Dim cmd As New SqlCommand
            Dim foo = From entry In availability Where entry.Value = lsvCreatedClasses.SelectedItems(0).SubItems.Item(1).Text Select entry.Key
            cmd.Connection = CN
            cmd.CommandText = "DeleteClass"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@CourseID", lsvCreatedClasses.SelectedItems(0).SubItems.Item(0).Text)
            cmd.Parameters.AddWithValue("@ClassTime", foo.First.ToString)

            'WB - tries to open the connection and reports the error if it fails.
            Try
                CN.Open()
            Catch ex As Exception
                MessageBox.Show("Cannot connect to Database - " & ex.Message, "Error")
            End Try

            'WB - clears the listbox of students
            lstClassesStudents.Items.Clear()

            'WB - executes the query and loads the Students names into lstStudents
            Try
                If cmd.ExecuteNonQuery = 0 Then
                    MessageBox.Show("Class was not deleted successfully", "Error")
                End If
            Catch ex As Exception
                MessageBox.Show("Class could not be deleted - " & ex.Message, "Error")
            Finally
                If CN.State = ConnectionState.Open Then CN.Close()
            End Try
            load_lsvCreatedClasses()
        End If
        btnGenerate_Click(btnDelete, e)
    End Sub
End Class
